
* +++++++++++++++++++++
* CLEAN DATA ON
* TEACHER UNEMPLOYMENT
* +++++++++++++++++++++

* Source: acquired from: Service-Haus der Bundesagentur fuer Arbeit
forval i = 2011/2020 {
	import excel "${data_raw}/teachers/315840_Koenen.xlsx", clear sheet("JD_`i'")
		
	ren (A-O) ///
		(ags job_seek_tot job_seek_teach job_seek_gen_schl job_seek_vocat ///
		job_seek_uni job_seek_teach_oth job_seek_drv_sprt ///
		unemp_tot unemp_teach unemp_gen_schl unemp_vocat ///
		unemp_uni unemp_teach_oth unemp_drv_sprt)
		
	keep if _n>=13 & _n<414

	split ags, parse(" ")
	destring ags1 job_seek* unemp*, replace 

	drop ags ags2-ags7
	ren ags1 ags

	gen year = `i'
	
	tempfile teachers`i'
	save `teachers`i''
	}
	
use `teachers2011', clear
forval i = 2012/2020 {
	append using `teachers`i''
}

ds unemp_* job_seek_*
local vars `r(varlist)'
order ags year, first
reshape wide job_seek* unemp*, i(ags) j(year) 

* when data is missing that's because there are no employed / job seeking people
* in that place and category --> replace to zero
foreach var of varlist job_seek* unemp_* {
	replace `var' = 0 if `var' ==. 
}

* normalize per pop (total and syrian)
ds * 
local initial_vars `r(varlist)'
preserve
	use "${data_derived}/kreis_total_pop_by_age_gender.dta", clear 
	reshape wide frac_for_tot-share_female, i(ags) j(year)
	forval year = 2011/2015 {
		replace pop_tot`year' = pop_tot2016 if ags ==3159 // for Göttingen use 2016 values because otherwise missing
		}
	tempfile pop
	save `pop'
restore 

merge 1:1 ags using `pop', nogen assert(3)

forval year = 2011/2019 {
	foreach var in `vars'  { 
		gen `var'`year'_per_pop = `var'`year' / pop_tot`year'
		gen `var'`year'_per_syr = `var'`year' / pop_syr_tot`year'
		gen `var'`year'_per_syr19 = `var'`year' / pop_syr_tot2019
		gen `var'`year'_per_syr17 = `var'`year' / pop_syr_tot2017
		gen `var'`year'_per_syr16 = `var'`year' / pop_syr_tot2016
		gen `var'`year'_per_syr15 = `var'`year' / pop_syr_tot2015
		local initial_vars "`initial_vars' `var'`year'_per_pop `var'`year'_per_syr `var'`year'_per_syr19 `var'`year'_per_syr17 `var'`year'_per_syr16 `var'`year'_per_syr15"
	}
}

* average over 2013/14 
foreach var in `vars'  { 
	gen `var'1314_per_pop = ((`var'2013+`var'2014)/2) / ((pop_tot2013 + pop_tot2014)/2)
	gen `var'1314_per_syr = ((`var'2013+`var'2014)/2)  / ((pop_syr_tot2013 + pop_syr_tot2014)/2)
	gen `var'1314_per_syr19 = ((`var'2013+`var'2014)/2)  / pop_syr_tot2019
	gen `var'1314_per_syr17 = ((`var'2013+`var'2014)/2)  / pop_syr_tot2017
	gen `var'1314_per_syr16 = ((`var'2013+`var'2014)/2)  / pop_syr_tot2016
	gen `var'1314_per_syr15 = ((`var'2013+`var'2014)/2)  / pop_syr_tot2015
	local initial_vars "`initial_vars' `var'1314_per_pop `var'1314_per_syr `var'1314_per_syr19 `var'1314_per_syr17 `var'1314_per_syr16 `var'1314_per_syr15"
}

* average 14 unemployment over 114-19 syrians 
foreach var in `vars'  { 
	gen `var'14_per_syr1419 = (`var'2014)  / ///
		((pop_syr_tot2014 + pop_syr_tot2015 + pop_syr_tot2016 + ///
		pop_syr_tot2017 + pop_syr_tot2018 + pop_syr_tot2019)/2)
	local initial_vars "`initial_vars' `var'14_per_syr1419"
}

keep `initial_vars' 

save "${data_derived}/unemployed_and_job_seeking_teachers2011_20.dta",replace
